{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8762dae0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np \n",
    "import pandas as pd\n",
    "from tqdm.notebook import tqdm\n",
    "\n",
    "path_root = '../input/optiver-realized-volatility-prediction'\n",
    "path_data = '../input/optiver-realized-volatility-prediction'\n",
    "path_submissions = '/'\n",
    "\n",
    "target_name = 'target'\n",
    "scores_folds = {}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1e3e445d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def log_return(list_stock_prices):\n",
    "    return np.log(list_stock_prices).diff() \n",
    "\n",
    "def realized_volatility(series_log_return):\n",
    "    return np.sqrt(np.sum(series_log_return**2))\n",
    "\n",
    "def rmspe(y_true, y_pred):\n",
    "    return  (np.sqrt(np.mean(np.square((y_true - y_pred) / y_true))))\n",
    "\n",
    "def calc_wap1(df):\n",
    "    wap = (df['bid_price1'] * df['ask_size1'] + df['ask_price1'] * df['bid_size1']) / (df['bid_size1'] + df['ask_size1'])\n",
    "    return wap\n",
    "\n",
    "def calc_wap2(df):\n",
    "    wap = (df['bid_price2'] * df['ask_size2'] + df['ask_price2'] * df['bid_size2']) / (df['bid_size2'] + df['ask_size2'])\n",
    "    return wap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3e091c33",
   "metadata": {},
   "outputs": [],
   "source": [
    "def book_features(df):\n",
    "    df['wap1'] = (df['bid_price1'] * df['ask_size1'] + df['ask_price1'] * df['bid_size1']) / (df['bid_size1'] + df['ask_size1'])\n",
    "    df['wap2'] = (df['bid_price2'] * df['ask_size2'] + df['ask_price2'] * df['bid_size2']) / (df['bid_size2'] + df['ask_size2'])\n",
    "    df['log_return1'] = df.groupby(['time_id'])['wap1'].apply(log_return).fillna(0)\n",
    "    df['log_return2'] = df.groupby(['time_id'])['wap2'].apply(log_return).fillna(0)\n",
    "    df['wap_balance'] = abs(df['wap1'] - df['wap2'])\n",
    "    df['price_spread'] = (df['ask_price1'] - df['bid_price1']) / ((df['ask_price1'] + df['bid_price1']) / 2)\n",
    "    df['price_spread2'] = (df['ask_price2'] - df['bid_price2']) / ((df['ask_price2'] + df['bid_price2']) / 2)\n",
    "    df['bid_spread'] = df['bid_price1'] - df['bid_price2']\n",
    "    df['ask_spread'] = df['ask_price1'] - df['ask_price2']\n",
    "    df[\"bid_ask_spread\"] = abs(df['bid_spread'] - df['ask_spread'])\n",
    "    df['total_volume'] = (df['ask_size1'] + df['ask_size2']) + (df['bid_size1'] + df['bid_size2'])\n",
    "    df['volume_imbalance'] = abs((df['ask_size1'] + df['ask_size2']) - (df['bid_size1'] + df['bid_size2']))\n",
    "    cols = ['time_id','wap1','wap2','log_return1','log_return2','price_spread','price_spread2',\n",
    "           'bid_spread','ask_spread','bid_ask_spread','total_volume','volume_imbalance']\n",
    "    return df[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "39ca9922",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "6cfd66963d434042b7b78868d3cd65e7",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/112 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "train = pd.read_csv(os.path.join(path_data, 'train.csv'))\n",
    "all_stock_ids = train['stock_id'].unique()\n",
    "\n",
    "dataType = 'train'\n",
    "\n",
    "dfs = []\n",
    "for stock_id in tqdm(all_stock_ids):\n",
    "    df1 = pd.read_parquet(os.path.join(path_data, 'book_{}.parquet/stock_id={}/'.format(dataType, stock_id)))\n",
    "    df1 = book_features(df1)\n",
    "\n",
    "\n",
    "    df1_mean = df1.groupby(['time_id']).mean()\n",
    "    df1_median = df1.groupby(['time_id']).median()\n",
    "    df1_std = df1.groupby(['time_id']).std()\n",
    "    df1_max = df1.groupby(['time_id']).max()\n",
    "    df1_min = df1.groupby(['time_id']).min()\n",
    "    df1_volatility = df1.groupby(['time_id'])[['log_return1','log_return2']].agg(realized_volatility)\n",
    "\n",
    "    df1_mean.columns = [x+'_mean' for x in df1_mean.columns]\n",
    "    df1_median.columns = [x+'_median' for x in df1_median.columns]\n",
    "    df1_std.columns = [x+'_std' for x in df1_std.columns]\n",
    "    df1_max.columns = [x+'_max' for x in df1_max.columns]\n",
    "    df1_min.columns = [x+'_min' for x in df1_min.columns]\n",
    "    df1_volatility.columns = [x+'_volatility' for x in df1_volatility]\n",
    "\n",
    "    df1_concat = pd.concat([df1_volatility,df1_mean,df1_median,df1_std,df1_max,df1_min],axis=1)\n",
    "\n",
    "\n",
    "    df2 = pd.read_parquet(os.path.join(path_data, 'trade_{}.parquet/stock_id={}/'.format(dataType, stock_id)))\n",
    "    df2 = df2[df2['size']>0].sort_values(by=['time_id','seconds_in_bucket']).reset_index(drop=True)\n",
    "    df2['trade_log_return1'] = df2.groupby(by = ['time_id'])['price'].apply(log_return).fillna(0)\n",
    "    df2 = df2[['time_id','trade_log_return1']].groupby(['time_id'])[['trade_log_return1']].agg(realized_volatility)\n",
    "    df2 = df2.rename(columns={'trade_log_return1':'trade_volatility'})\n",
    "\n",
    "    df = pd.concat([df2,df1_concat],axis=1)\n",
    "    df['stock_id'] = stock_id\n",
    "    df = df.reset_index()\n",
    "    df.index = df[['stock_id','time_id']].apply(lambda x:str(x[0])+'_'+str(x[1]),axis=1)\n",
    "    df = df.drop(['stock_id','time_id'],axis=1)\n",
    "    dfs.append(df)\n",
    "    \n",
    "    \n",
    "dfs = pd.concat(dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d15c826f",
   "metadata": {},
   "outputs": [],
   "source": [
    "train = pd.read_csv(os.path.join(path_data, 'train.csv'))\n",
    "train.index = train[['stock_id','time_id']].apply(lambda x:str(x[0])+'_'+str(x[1]),axis=1)\n",
    "train = train.drop(['stock_id','time_id'],axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a840d3dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "dff = pd.concat([train,dfs],axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "3e79bd75",
   "metadata": {},
   "outputs": [],
   "source": [
    "dff.to_csv('dff.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "ad22f9a1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>target</th>\n",
       "      <th>trade_volatility</th>\n",
       "      <th>log_return1_volatility</th>\n",
       "      <th>log_return2_volatility</th>\n",
       "      <th>wap1_mean</th>\n",
       "      <th>wap2_mean</th>\n",
       "      <th>log_return1_mean</th>\n",
       "      <th>log_return2_mean</th>\n",
       "      <th>price_spread_mean</th>\n",
       "      <th>price_spread2_mean</th>\n",
       "      <th>...</th>\n",
       "      <th>wap2_min</th>\n",
       "      <th>log_return1_min</th>\n",
       "      <th>log_return2_min</th>\n",
       "      <th>price_spread_min</th>\n",
       "      <th>price_spread2_min</th>\n",
       "      <th>bid_spread_min</th>\n",
       "      <th>ask_spread_min</th>\n",
       "      <th>bid_ask_spread_min</th>\n",
       "      <th>total_volume_min</th>\n",
       "      <th>volume_imbalance_min</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0_5</th>\n",
       "      <td>0.004136</td>\n",
       "      <td>0.002006</td>\n",
       "      <td>0.004499</td>\n",
       "      <td>0.006999</td>\n",
       "      <td>1.003725</td>\n",
       "      <td>1.003661</td>\n",
       "      <td>7.588485e-06</td>\n",
       "      <td>0.000008</td>\n",
       "      <td>0.000852</td>\n",
       "      <td>0.001177</td>\n",
       "      <td>...</td>\n",
       "      <td>1.001390</td>\n",
       "      <td>-0.000896</td>\n",
       "      <td>-0.001827</td>\n",
       "      <td>0.000361</td>\n",
       "      <td>0.000670</td>\n",
       "      <td>0.000052</td>\n",
       "      <td>-0.000569</td>\n",
       "      <td>0.000103</td>\n",
       "      <td>34</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0_11</th>\n",
       "      <td>0.001445</td>\n",
       "      <td>0.000901</td>\n",
       "      <td>0.001204</td>\n",
       "      <td>0.002476</td>\n",
       "      <td>1.000239</td>\n",
       "      <td>1.000206</td>\n",
       "      <td>1.801324e-06</td>\n",
       "      <td>0.000004</td>\n",
       "      <td>0.000394</td>\n",
       "      <td>0.000671</td>\n",
       "      <td>...</td>\n",
       "      <td>0.999575</td>\n",
       "      <td>-0.000476</td>\n",
       "      <td>-0.000547</td>\n",
       "      <td>0.000151</td>\n",
       "      <td>0.000301</td>\n",
       "      <td>0.000050</td>\n",
       "      <td>-0.000351</td>\n",
       "      <td>0.000100</td>\n",
       "      <td>46</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0_16</th>\n",
       "      <td>0.002168</td>\n",
       "      <td>0.001961</td>\n",
       "      <td>0.002369</td>\n",
       "      <td>0.004801</td>\n",
       "      <td>0.999542</td>\n",
       "      <td>0.999680</td>\n",
       "      <td>-1.103269e-05</td>\n",
       "      <td>-0.000008</td>\n",
       "      <td>0.000725</td>\n",
       "      <td>0.001120</td>\n",
       "      <td>...</td>\n",
       "      <td>0.996897</td>\n",
       "      <td>-0.000783</td>\n",
       "      <td>-0.001612</td>\n",
       "      <td>0.000384</td>\n",
       "      <td>0.000575</td>\n",
       "      <td>0.000048</td>\n",
       "      <td>-0.000718</td>\n",
       "      <td>0.000096</td>\n",
       "      <td>108</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0_31</th>\n",
       "      <td>0.002195</td>\n",
       "      <td>0.001561</td>\n",
       "      <td>0.002574</td>\n",
       "      <td>0.003637</td>\n",
       "      <td>0.998832</td>\n",
       "      <td>0.998633</td>\n",
       "      <td>-2.356919e-05</td>\n",
       "      <td>-0.000017</td>\n",
       "      <td>0.000860</td>\n",
       "      <td>0.001159</td>\n",
       "      <td>...</td>\n",
       "      <td>0.997430</td>\n",
       "      <td>-0.001296</td>\n",
       "      <td>-0.001601</td>\n",
       "      <td>0.000324</td>\n",
       "      <td>0.000648</td>\n",
       "      <td>0.000046</td>\n",
       "      <td>-0.000463</td>\n",
       "      <td>0.000093</td>\n",
       "      <td>140</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0_62</th>\n",
       "      <td>0.001747</td>\n",
       "      <td>0.000871</td>\n",
       "      <td>0.001894</td>\n",
       "      <td>0.003257</td>\n",
       "      <td>0.999619</td>\n",
       "      <td>0.999626</td>\n",
       "      <td>-1.015897e-08</td>\n",
       "      <td>-0.000002</td>\n",
       "      <td>0.000397</td>\n",
       "      <td>0.000697</td>\n",
       "      <td>...</td>\n",
       "      <td>0.999102</td>\n",
       "      <td>-0.000750</td>\n",
       "      <td>-0.000787</td>\n",
       "      <td>0.000093</td>\n",
       "      <td>0.000373</td>\n",
       "      <td>0.000047</td>\n",
       "      <td>-0.000420</td>\n",
       "      <td>0.000093</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126_32751</th>\n",
       "      <td>0.003461</td>\n",
       "      <td>0.002171</td>\n",
       "      <td>0.003691</td>\n",
       "      <td>0.005876</td>\n",
       "      <td>0.999582</td>\n",
       "      <td>0.999585</td>\n",
       "      <td>-1.701452e-06</td>\n",
       "      <td>-0.000003</td>\n",
       "      <td>0.000878</td>\n",
       "      <td>0.001171</td>\n",
       "      <td>...</td>\n",
       "      <td>0.997950</td>\n",
       "      <td>-0.001228</td>\n",
       "      <td>-0.001287</td>\n",
       "      <td>0.000392</td>\n",
       "      <td>0.000457</td>\n",
       "      <td>0.000033</td>\n",
       "      <td>-0.000882</td>\n",
       "      <td>0.000065</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126_32753</th>\n",
       "      <td>0.003113</td>\n",
       "      <td>0.002180</td>\n",
       "      <td>0.004104</td>\n",
       "      <td>0.004991</td>\n",
       "      <td>1.002476</td>\n",
       "      <td>1.002602</td>\n",
       "      <td>1.989105e-05</td>\n",
       "      <td>0.000022</td>\n",
       "      <td>0.000706</td>\n",
       "      <td>0.000974</td>\n",
       "      <td>...</td>\n",
       "      <td>1.000632</td>\n",
       "      <td>-0.001061</td>\n",
       "      <td>-0.000891</td>\n",
       "      <td>0.000240</td>\n",
       "      <td>0.000515</td>\n",
       "      <td>0.000034</td>\n",
       "      <td>-0.000586</td>\n",
       "      <td>0.000069</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126_32758</th>\n",
       "      <td>0.004070</td>\n",
       "      <td>0.001921</td>\n",
       "      <td>0.003117</td>\n",
       "      <td>0.006020</td>\n",
       "      <td>1.001082</td>\n",
       "      <td>1.000996</td>\n",
       "      <td>5.955717e-06</td>\n",
       "      <td>0.000010</td>\n",
       "      <td>0.000739</td>\n",
       "      <td>0.001119</td>\n",
       "      <td>...</td>\n",
       "      <td>0.999515</td>\n",
       "      <td>-0.000875</td>\n",
       "      <td>-0.001372</td>\n",
       "      <td>0.000148</td>\n",
       "      <td>0.000444</td>\n",
       "      <td>0.000049</td>\n",
       "      <td>-0.000494</td>\n",
       "      <td>0.000099</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126_32763</th>\n",
       "      <td>0.003357</td>\n",
       "      <td>0.002051</td>\n",
       "      <td>0.003661</td>\n",
       "      <td>0.005362</td>\n",
       "      <td>1.001809</td>\n",
       "      <td>1.001790</td>\n",
       "      <td>6.413158e-07</td>\n",
       "      <td>0.000002</td>\n",
       "      <td>0.000530</td>\n",
       "      <td>0.000806</td>\n",
       "      <td>...</td>\n",
       "      <td>1.000537</td>\n",
       "      <td>-0.000607</td>\n",
       "      <td>-0.000955</td>\n",
       "      <td>0.000066</td>\n",
       "      <td>0.000329</td>\n",
       "      <td>0.000066</td>\n",
       "      <td>-0.000527</td>\n",
       "      <td>0.000132</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>126_32767</th>\n",
       "      <td>0.002090</td>\n",
       "      <td>0.001041</td>\n",
       "      <td>0.002092</td>\n",
       "      <td>0.003037</td>\n",
       "      <td>1.000272</td>\n",
       "      <td>1.000367</td>\n",
       "      <td>-3.348337e-06</td>\n",
       "      <td>-0.000006</td>\n",
       "      <td>0.000432</td>\n",
       "      <td>0.000699</td>\n",
       "      <td>...</td>\n",
       "      <td>0.999129</td>\n",
       "      <td>-0.000447</td>\n",
       "      <td>-0.000723</td>\n",
       "      <td>0.000154</td>\n",
       "      <td>0.000309</td>\n",
       "      <td>0.000051</td>\n",
       "      <td>-0.000463</td>\n",
       "      <td>0.000103</td>\n",
       "      <td>59</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>428932 rows × 59 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             target  trade_volatility  log_return1_volatility  \\\n",
       "0_5        0.004136          0.002006                0.004499   \n",
       "0_11       0.001445          0.000901                0.001204   \n",
       "0_16       0.002168          0.001961                0.002369   \n",
       "0_31       0.002195          0.001561                0.002574   \n",
       "0_62       0.001747          0.000871                0.001894   \n",
       "...             ...               ...                     ...   \n",
       "126_32751  0.003461          0.002171                0.003691   \n",
       "126_32753  0.003113          0.002180                0.004104   \n",
       "126_32758  0.004070          0.001921                0.003117   \n",
       "126_32763  0.003357          0.002051                0.003661   \n",
       "126_32767  0.002090          0.001041                0.002092   \n",
       "\n",
       "           log_return2_volatility  wap1_mean  wap2_mean  log_return1_mean  \\\n",
       "0_5                      0.006999   1.003725   1.003661      7.588485e-06   \n",
       "0_11                     0.002476   1.000239   1.000206      1.801324e-06   \n",
       "0_16                     0.004801   0.999542   0.999680     -1.103269e-05   \n",
       "0_31                     0.003637   0.998832   0.998633     -2.356919e-05   \n",
       "0_62                     0.003257   0.999619   0.999626     -1.015897e-08   \n",
       "...                           ...        ...        ...               ...   \n",
       "126_32751                0.005876   0.999582   0.999585     -1.701452e-06   \n",
       "126_32753                0.004991   1.002476   1.002602      1.989105e-05   \n",
       "126_32758                0.006020   1.001082   1.000996      5.955717e-06   \n",
       "126_32763                0.005362   1.001809   1.001790      6.413158e-07   \n",
       "126_32767                0.003037   1.000272   1.000367     -3.348337e-06   \n",
       "\n",
       "           log_return2_mean  price_spread_mean  price_spread2_mean  ...  \\\n",
       "0_5                0.000008           0.000852            0.001177  ...   \n",
       "0_11               0.000004           0.000394            0.000671  ...   \n",
       "0_16              -0.000008           0.000725            0.001120  ...   \n",
       "0_31              -0.000017           0.000860            0.001159  ...   \n",
       "0_62              -0.000002           0.000397            0.000697  ...   \n",
       "...                     ...                ...                 ...  ...   \n",
       "126_32751         -0.000003           0.000878            0.001171  ...   \n",
       "126_32753          0.000022           0.000706            0.000974  ...   \n",
       "126_32758          0.000010           0.000739            0.001119  ...   \n",
       "126_32763          0.000002           0.000530            0.000806  ...   \n",
       "126_32767         -0.000006           0.000432            0.000699  ...   \n",
       "\n",
       "           wap2_min  log_return1_min  log_return2_min  price_spread_min  \\\n",
       "0_5        1.001390        -0.000896        -0.001827          0.000361   \n",
       "0_11       0.999575        -0.000476        -0.000547          0.000151   \n",
       "0_16       0.996897        -0.000783        -0.001612          0.000384   \n",
       "0_31       0.997430        -0.001296        -0.001601          0.000324   \n",
       "0_62       0.999102        -0.000750        -0.000787          0.000093   \n",
       "...             ...              ...              ...               ...   \n",
       "126_32751  0.997950        -0.001228        -0.001287          0.000392   \n",
       "126_32753  1.000632        -0.001061        -0.000891          0.000240   \n",
       "126_32758  0.999515        -0.000875        -0.001372          0.000148   \n",
       "126_32763  1.000537        -0.000607        -0.000955          0.000066   \n",
       "126_32767  0.999129        -0.000447        -0.000723          0.000154   \n",
       "\n",
       "           price_spread2_min  bid_spread_min  ask_spread_min  \\\n",
       "0_5                 0.000670        0.000052       -0.000569   \n",
       "0_11                0.000301        0.000050       -0.000351   \n",
       "0_16                0.000575        0.000048       -0.000718   \n",
       "0_31                0.000648        0.000046       -0.000463   \n",
       "0_62                0.000373        0.000047       -0.000420   \n",
       "...                      ...             ...             ...   \n",
       "126_32751           0.000457        0.000033       -0.000882   \n",
       "126_32753           0.000515        0.000034       -0.000586   \n",
       "126_32758           0.000444        0.000049       -0.000494   \n",
       "126_32763           0.000329        0.000066       -0.000527   \n",
       "126_32767           0.000309        0.000051       -0.000463   \n",
       "\n",
       "           bid_ask_spread_min  total_volume_min  volume_imbalance_min  \n",
       "0_5                  0.000103                34                     0  \n",
       "0_11                 0.000100                46                     1  \n",
       "0_16                 0.000096               108                     1  \n",
       "0_31                 0.000093               140                     2  \n",
       "0_62                 0.000093                16                     0  \n",
       "...                       ...               ...                   ...  \n",
       "126_32751            0.000065                 5                     0  \n",
       "126_32753            0.000069                16                     0  \n",
       "126_32758            0.000099                18                     1  \n",
       "126_32763            0.000132                42                     0  \n",
       "126_32767            0.000103                59                     1  \n",
       "\n",
       "[428932 rows x 59 columns]"
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dff/omdex"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c37187f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "f701b167",
   "metadata": {},
   "outputs": [],
   "source": [
    "y = dff['target']\n",
    "X = dff.drop(['target'],axis=1)\n",
    "cols = ['trade_volatility','log_return1_volatility','log_return2_volatility']\n",
    "X = dff[cols]\n",
    "\n",
    "X_train = X[:int(0.7*len(X))]\n",
    "y_train = y[:int(0.7*len(y))]\n",
    "X_test = X[int(0.7*len(X)):]\n",
    "y_test = y[int(0.7*len(y)):]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd4ec7c6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cb74e3e7",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "95ef554a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "05819c7c70734e78b3487ef2134f0ad2",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "MetricVisualizer(layout=Layout(align_self='stretch', height='500px'))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from catboost import CatBoostRegressor, Pool, metrics, cv\n",
    "\n",
    "\n",
    "model = CatBoostRegressor(\n",
    "    random_seed=2333,\n",
    "    iterations = 500,\n",
    "    logging_level='Silent'\n",
    ")\n",
    "\n",
    "model.fit(\n",
    "    X_train, y_train,\n",
    "    eval_set=(X_test, y_test),\n",
    "    plot=True\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "6cd67a18",
   "metadata": {},
   "outputs": [],
   "source": [
    "preds = model.predict(X_test)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "0f3bc47a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2731817205807334"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rmspe(y_test, preds)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "883586cc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.28020368412961494"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rmspe(y_test, preds)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "dcd286d7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2750490212266169"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rmspe(y_test, preds)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "4f36e634",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2754638261622671"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rmspe(y_test, preds)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "ccf41980",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2746562148581265"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rmspe(y_test, preds)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "af6b8327",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_result = pd.DataFrame([y_test.values,preds]).T\n",
    "df_result.columns = ['real','pred']\n",
    "df_result = df_result.sort_values(by=['real']).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "f898ad0e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.07904696628139923"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pp = (df_result['pred'] - df_result['real'])\n",
    "pp.mean()*10000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "3717ba03",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.1819403032801456"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pp = (df_result['pred'] - df_result['real'])\n",
    "pp.mean()*10000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cd9a1cbe",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c6c0dedb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c9dd63d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45ef5dec",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "5f6c4130",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2746562148581265"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rmspe(y_test, preds)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a490dbc1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "5e54f01d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.00413577, 0.00144459, 0.00216819, ..., 0.00214726, 0.00399697,\n",
       "       0.00188751])"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y_train.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "acb6932f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[2.92757130e-03, 4.90802154e-03, 5.42652188e-03, ...,\n",
       "        5.87701797e-05, 4.00000000e+00, 0.00000000e+00],\n",
       "       [3.58701893e-03, 5.60135441e-03, 8.37401673e-03, ...,\n",
       "        5.84125519e-05, 3.00000000e+01, 0.00000000e+00],\n",
       "       [2.46543018e-03, 4.04429156e-03, 7.38867652e-03, ...,\n",
       "        6.05583191e-05, 2.50000000e+01, 0.00000000e+00],\n",
       "       ...,\n",
       "       [1.92089751e-03, 3.11747054e-03, 6.01962116e-03, ...,\n",
       "        9.87052917e-05, 1.80000000e+01, 1.00000000e+00],\n",
       "       [2.05061538e-03, 3.66106909e-03, 5.36200264e-03, ...,\n",
       "        1.31607056e-04, 4.20000000e+01, 0.00000000e+00],\n",
       "       [1.04126986e-03, 2.09153863e-03, 3.03656841e-03, ...,\n",
       "        1.02758408e-04, 5.90000000e+01, 1.00000000e+00]])"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "y_test.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8441e321",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3046639b",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fa31215",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0d6843a8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d86aa73a",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "f36f641e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.read_parquet(os.path.join(path_data, 'trade_{}.parquet/stock_id={}/'.format(dataType, stock_id)))\n",
    "df2 = df2[df2['size']>0].sort_values(by=['time_id','seconds_in_bucket']).reset_index(drop=True)\n",
    "df2['trade_log_return1'] = df2.groupby(by = ['time_id'])['price'].apply(log_return).fillna(0)\n",
    "df2 = pd.DataFrame(df2.groupby(['time_id'])[['trade_log_return1']].agg(realized_volatility).reset_index())\n",
    "df2 = df2.rename(columns={'trade_log_return1':'pred'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "44612004",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time_id</th>\n",
       "      <th>pred</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5</td>\n",
       "      <td>0.003959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>0.001177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16</td>\n",
       "      <td>0.002829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>31</td>\n",
       "      <td>0.001940</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>62</td>\n",
       "      <td>0.001311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3825</th>\n",
       "      <td>32751</td>\n",
       "      <td>0.001375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3826</th>\n",
       "      <td>32753</td>\n",
       "      <td>0.002932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3827</th>\n",
       "      <td>32758</td>\n",
       "      <td>0.001982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3828</th>\n",
       "      <td>32763</td>\n",
       "      <td>0.003066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3829</th>\n",
       "      <td>32767</td>\n",
       "      <td>0.002058</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3830 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      time_id      pred\n",
       "0           5  0.003959\n",
       "1          11  0.001177\n",
       "2          16  0.002829\n",
       "3          31  0.001940\n",
       "4          62  0.001311\n",
       "...       ...       ...\n",
       "3825    32751  0.001375\n",
       "3826    32753  0.002932\n",
       "3827    32758  0.001982\n",
       "3828    32763  0.003066\n",
       "3829    32767  0.002058\n",
       "\n",
       "[3830 rows x 2 columns]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5ce94ef8",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ead0de11",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "c530aa0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "dataType = 'train'\n",
    "stock_id = 13\n",
    "df2 = pd.read_parquet(os.path.join(path_data, 'trade_{}.parquet/stock_id={}/'.format(dataType, stock_id)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "f960034d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.read_parquet(os.path.join(path_data, 'trade_{}.parquet/stock_id={}/'.format(dataType, stock_id)))\n",
    "df2 = df2[df2['size']>0].sort_values(by=['time_id','seconds_in_bucket']).reset_index(drop=True)\n",
    "df2['trade_log_return1'] = df2.groupby(by = ['time_id'])['price'].apply(log_return).fillna(0)\n",
    "df2 = pd.DataFrame(df2.groupby(['time_id'])[['trade_log_return1']].agg(realized_volatility).reset_index())\n",
    "df2 = df2.rename(columns={'trade_log_return1':'pred'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "53ec90f2",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42b61b83",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "061700f4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.5490748396281947"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ddd = pd.merge(train[train['stock_id']==11],df2,on=['time_id'])\n",
    "rmspe(ddd['target'],ddd['pred'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "0ab59478",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.5485148131641088"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ddd = pd.merge(train[train['stock_id']==11],df2,on=['time_id'])\n",
    "rmspe(ddd['target'],ddd['pred'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bacd6c46",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cfb621ca",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
